import java.io.File;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Clob;
import java.sql.Statement;

import javax.sql.rowset.serial.SerialClob;

public class XMLReader {
	
	private ArrayList<PreparedStatement> statements = new ArrayList<PreparedStatement>();
	private Connection conn;
	private String fileName;
	
	public ArrayList<PreparedStatement> getStatements(){
		return statements;
	}
	public XMLReader(Connection pConn, String pFileName){
		conn = pConn;
		fileName = pFileName;
	}
	
	public void parseFile(){
		try {
			File file = new File(fileName);
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document doc = db.parse(file);
			doc.getDocumentElement().normalize();
			
			NodeList nodeLst = doc.getElementsByTagName("subject");

			for (int s = 0; s < nodeLst.getLength(); s++) {
				Node fstNode = nodeLst.item(s);
				if (fstNode.getNodeType() == Node.ELEMENT_NODE) {
					try{
						PreparedStatement ps = conn.prepareStatement("insert into subject(subjectCode,subjectName,semester,yearRunning,description,convenorName,convenorEmail) values(?,?,?,?,?,?,?)");

						ps.setString(1, getNodeValue((Element) fstNode,"subjectCode")) ;
						ps.setString(2, getNodeValue((Element) fstNode,"subjectName"));
						int sem =0;
						if(!getNodeValue((Element) fstNode,"semester").isEmpty()){
							sem = Integer.parseInt(getNodeValue((Element) fstNode,"semester"));
						}
						int yr=0;
						if(!getNodeValue((Element) fstNode,"yearRunning").isEmpty()){
							yr = Integer.parseInt(getNodeValue((Element) fstNode,"yearRunning"));
						}
						ps.setInt(3, sem);
						ps.setInt(4, yr);
						Clob clob = null;
						clob = new SerialClob(getNodeValue((Element) fstNode,"description").toCharArray());
						ps.setClob(5, clob);
						ps.setString(6, getNodeValue((Element) fstNode,"convenorName"));
						ps.setString(7, getNodeValue((Element) fstNode,"convenorEmail"));
						statements.add(ps);
					} catch (SQLException e) { 
						Object x = null;
					}
				}
			}		
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private String getNodeValue(Element pElement, String pTagName){
		try{
			NodeList elementList = pElement.getElementsByTagName(pTagName);
			Element element = (Element) elementList.item(0);
			NodeList targetElement = element.getChildNodes();
			return ((Node) targetElement.item(0)).getNodeValue();
		} catch (Exception e) {
			return null;
		}
	}
}
